﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
#if!__MonoCS__
using Microsoft.Office.Interop;
using Outlook = Microsoft.Office.Interop.Outlook;
#endif
using System.Runtime.InteropServices;
using Microsoft.Exchange.WebServices;
using Microsoft.Exchange.WebServices.Data;
using Microsoft.Exchange.WebServices.Authentication;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.ComponentModel;



namespace Contacts
{
	public class func
	{
		public static DataTable origconpull { get; set; }
		public static DataTable origcompull { get; set; }
		public static DataRow fixdate { get; set; }
		public static DataRow update { get; set; }
		public static string button { get; set; }
		public static string num;
		public static object[] dtables;
		// TODO: variable proj seems to get reassigned several time across various scopes
		public static DataTable proj;
		private static DataRow[] filter;
		private static string foldername;
		private static DataTable projcont;
		private static SpreadsheetDocument xl;
		private static WorkbookPart workbook;
		private static WorksheetPart worksheet;
		private static SharedStringTablePart sharedstring;
		private static SharedStringTable sst;
		private static Workbook wb;
		private static Worksheet ws;
		private static Sheets sheets;
		private static Sheet sheet;
		private static WorkbookStylesPart styles;
		private static HeaderFooter hf;
		private static PageSetup pagesetup;
		private static SheetProperties sp;
		private static PageSetupProperties pagesetupprop;
		private static Columns columns;
		private static TableColumns tablecolumns;
		private static Table table;
		private static TableParts tps;
		private static TablePart tp;
		private static TableColumn tablecolumn;
		private static ProgressBar pb2;
		private static Label lab_save;
		private static BackgroundWorker worker;
		private static int dgvrc;
		

		public static string removecharacters(string phone)
			
		{
			StringBuilder sb = new StringBuilder();
			num = " ";
			for (int i = 0; i < phone.Length; i++)
			{
				if ((phone[i] >= '0' && phone[i] <= '9'))
				{sb.Append(phone[i]);}
			}
			phone = sb.ToString();
			return phone;
		}

		public static string area(string phone)
		{
			string p1 = phone.Substring(0, 3);
			return p1;
		}

		public static string prefix(string phone)
		{
			string p2 = phone.Substring(3, 3);
			return p2;
		}

		public static string line(string phone)
		{
			string p3 = phone.Substring(6, 4);
			return p3;
		}

		public static string ext(string phone)
		{
			string p4 = " ";
			if (phone.Length > 10)
			{
				p4 = phone.Substring(10, phone.Length - 10);
				return p4;
			}
			return p4;
		}

		public static bool d3(string digit)
		{
			string d = digit;
			int i;
			bool result = int.TryParse(d, out i);
			if (result = true && d.Length >= 3)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		public static bool d4(string digit)
		{
			string d = digit;
			int i;
			bool result = int.TryParse(d, out i);
			if (result = true && d.Length >= 4)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		public static void FillRows(DataSet dscont, DataGridView dgv, ProgressBar pb, Label lab_save)
		{
			for (int i = 0; i < dgv.Rows.Count; i++)
			{
				try
				{
					if (pb != null)
					{ pb.PerformStep(); }
					if (lab_save != null)
					{
						lab_save.Text = "Reconstructing this view...";
						lab_save.Refresh();
					}
					string jobrole = Convert.ToString(dgv.Rows[i].Cells["job_role_id"].Value);
					string c = Convert.ToString(dgv.Rows[i].Cells["contact_id"].Value);
					if (!String.IsNullOrEmpty(jobrole) && !String.IsNullOrEmpty(c))
					{
						dgv.Rows[i].Cells[0].Value = dgv.Rows[i].Cells["job_role_id"].Value;
						dgv.Rows[i].Cells[1].Value = dgv.Rows[i].Cells["contact_id"].Value;
						object cv = dgv.Rows[i].Cells["contact_id"].Value;
						DataRow conta = dscont.Tables["contacts"].Rows.Find(cv);
						DataRow compa = dscont.Tables["companies"].Rows.Find(conta.ItemArray[5]);
						int category = (int)compa.ItemArray[9];
						DataRow cata = dscont.Tables["categories"].Rows.Find(category);
						dgv.Rows[i].Cells[5].Value = conta.ItemArray[4];
						dgv.Rows[i].Cells[6].Value = compa.ItemArray[1];
						dgv.Rows[i].Cells[7].Value = compa.ItemArray[2];
						dgv.Rows[i].Cells[8].Value = compa.ItemArray[3];
						dgv.Rows[i].Cells[9].Value = compa.ItemArray[4];
						dgv.Rows[i].Cells[10].Value = compa.ItemArray[5];
						dgv.Rows[i].Cells[11].Value = compa.ItemArray[6];
						dgv.Rows[i].Cells[12].Value = conta.ItemArray[6];
						dgv.Rows[i].Cells[13].Value = compa.ItemArray[7];
						dgv.Rows[i].Cells[14].Value = compa.ItemArray[8];
						dgv.Rows[i].Cells[15].Value = conta.ItemArray[7];
						dgv.Rows[i].Cells[16].Value = conta.ItemArray[8];
						dgv.Rows[i].Cells[17].Value = cata.ItemArray[1];
					}
				}
				
				catch (Exception ex)
				{
					string subject = "func Fill Rows Error";
					ErrorHandling.ErrorEmail(subject, ex);
				}
			}
			
		}

		public static void FillFolder (DataSet dscont, DataGridView dgv, int pi, ProjectTypes ProjectType, ProgressBar pb2, Label lab_save)
		{
			try {
				dgvrc = dgv.Rows.Count;
				//pb2 = progbar;
				//lab_save = lab_sa;
				ExchangeService service = Service.ConnectToService (
					UserData.CreateUserData ("dplummer@rhaaia.com"),
					new TraceListener ()
				);
				StoreProgramTypes programtype = func.GetStoreProgramType(ProjectType);
				switch (programtype){
					case StoreProgramTypes.WalMartRealty:
						{
							proj = dscont.Tables [programtype.ToString()];
						}
						break;
						
					case StoreProgramTypes.StoreLayout:
						{
							proj = dscont.Tables ["project_remodels"];
						}
						break;
				}
				//Guid propertySetId = new Guid("{00062004-0000-0000-C000-000000000046}");
				//ExtendedPropertyDefinition StoreNumber = new ExtendedPropertyDefinition(propertySetId, "Str No", MapiPropertyType.String);
				//ExtendedPropertyDefinition StoreSeq = new ExtendedPropertyDefinition(propertySetId, "Str Seq", MapiPropertyType.String);
				//ExtendedPropertyDefinition StoreCity = new ExtendedPropertyDefinition(propertySetId, "Str City", MapiPropertyType.String);
				//ExtendedPropertyDefinition StoreSt = new ExtendedPropertyDefinition(propertySetId, "Str St", MapiPropertyType.String);
				//ExtendedPropertyDefinition JobRole = new ExtendedPropertyDefinition(propertySetId, "Job Role", MapiPropertyType.String);
				//ExtendedPropertyDefinition Suite = new ExtendedPropertyDefinition(propertySetId, "Suite", MapiPropertyType.String);
				//ExtendedPropertyDefinition StoreLocation = new ExtendedPropertyDefinition(propertySetId, 0x804F, MapiPropertyType.String);
				FolderView vw = new Microsoft.Exchange.WebServices.Data.FolderView (int.MaxValue);
				FindFoldersResults results = service.FindFolders (WellKnownFolderName.PublicFoldersRoot, vw);
				ContactsFolder contactsfolder = (ContactsFolder)results.Folders [1];
				FolderId id = contactsfolder.Id;
				string str = "" + proj.Rows [pi] ["str_city"] + " " + proj.Rows [pi] ["str_st"] +
					" " + proj.Rows [pi] ["str_num"] + " - " + proj.Rows [pi] ["str_seq"] + "";
				FolderView fv = new Microsoft.Exchange.WebServices.Data.FolderView (int.MaxValue);
				FindFoldersResults contactsfolders = service.FindFolders (id, fv);
				//PropertySet projectviewprops = new PropertySet(StoreNumber, StoreSeq, StoreCity, StoreSt, JobRole,
				//    ContactSchema.JobTitle, ContactSchema.CompanyName, ContactSchema.BusinessAddressStreet, Suite, ContactSchema.BusinessAddressCity,
				//    ContactSchema.BusinessAddressState, ContactSchema.BusinessAddressPostalCode, ContactSchema.BusinessPhone, ContactSchema.BusinessPhone2,
				//    ContactSchema.BusinessFax, ContactSchema.MobilePhone, ContactSchema.EmailAddress1, ContactSchema.Categories);
				FindFoldersResults contfldr = null;

				if (Enumerable.Range(1, 16383).Contains((int)ProjectType)) {
							projcont = dscont.Tables ["project_contacts"];
							ContactsFolder storecontactsfldr = (ContactsFolder)contactsfolders.Folders [1];
							FolderView scfv = new Microsoft.Exchange.WebServices.Data.FolderView (int.MaxValue);
							FolderId scfid = storecontactsfldr.Id;
							contfldr = service.FindFolders (scfid, scfv);
						} else 	{
							projcont = dscont.Tables["project_contacts_remodels"];
						}
				
				int count = contfldr.Folders.Count;
				
				for (int i = 0; i < count; i++)
				{
					string fldrname = contfldr.Folders[i].DisplayName;

					if (fldrname == str)
					{
						ContactsFolder project = (ContactsFolder)contfldr.Folders[i];
						FolderId olfid = project.Id;
						ItemView iv = new ItemView(int.MaxValue);
						FindItemsResults<Microsoft.Exchange.WebServices.Data.Item> items = service.FindItems(olfid, iv);
						int dcc = items.Items.Count;
						if (dcc > 0)
						{
							int inc = 0;
							for (int dc = dcc - 1; dc >= 0; dc--)
							{
								//inc++;
								pb2.PerformStep();
								pb2.Refresh();
								lab_save.Text = "Deleting contacts in outlook...";
								lab_save.Refresh();
								//backgw.ReportProgress(inc/(dgvrc * 3), "Deleting contacts in Outlook...");
								items.Items[dc].Delete(DeleteMode.HardDelete);
							}
						}

						int strid = (int)proj.Rows[pi]["str_id"];
						DataRow[] filter = projcont.Select(string.Format("str_id = {0}", strid));
						int filtercount = filter.Length;
						items = service.FindItems(olfid, iv);

						for (int c = 0; c < filtercount; c++)
						{
							pb2.PerformStep();
							pb2.Refresh();
							lab_save.Text = "Rebuilding contacts in Outlook...";
							lab_save.Refresh();
							DataRow jr = dscont.Tables["job_roles"].Rows.Find(filter[c]["job_role_id"]);
							DataRow cont = dscont.Tables["contacts"].Rows.Find(filter[c]["contact_id"]);
							DataRow comp = dscont.Tables["companies"].Rows.Find(cont["company_id"]);
							Contact contact = new Contact(service);
							contact.ItemClass = "IPM.Contact.ProjectContactForm";
							contact.OfficeLocation = "" + proj.Rows[pi]["str_num"] + "-" + proj.Rows[pi]["str_seq"] +
								" " + proj.Rows[pi]["str_city"] + ", " + proj.Rows[pi]["str_st"] + "";
							contact.Profession = jr["job_role"].ToString();
							contact.GivenName = cont["first_name"].ToString();
							if (!String.IsNullOrEmpty(cont["middle_name"].ToString()))
							{ contact.MiddleName = cont["middle_name"].ToString(); }
							contact.Surname = cont["last_name"].ToString();
							if (!String.IsNullOrEmpty(cont["job_title"].ToString()))
							{ contact.JobTitle = cont["job_title"].ToString(); }
							contact.CompanyName = comp["company_name"].ToString();
							PhysicalAddressEntry pae = new PhysicalAddressEntry();
							if (comp["suite"].ToString() == "")
							{
								pae.Street = comp["street"].ToString();
							}
							else
							{
								pae.Street = "" + comp["street"].ToString() + ",  " + comp["suite"].ToString();
							}
							pae.City = comp["city"].ToString();
							pae.State = comp["state"].ToString();
							pae.PostalCode = comp["zip"].ToString();
							contact.PhysicalAddresses[PhysicalAddressKey.Business] = pae;
							if (!String.IsNullOrEmpty(cont["direct_phone"].ToString()))
							{ contact.PhoneNumbers[PhoneNumberKey.BusinessPhone] = cont["direct_phone"].ToString(); }
							contact.PhoneNumbers[PhoneNumberKey.BusinessPhone2] = comp["main_phone"].ToString();
							if (!String.IsNullOrEmpty(comp["fax"].ToString()))
							{ contact.PhoneNumbers[PhoneNumberKey.BusinessFax] = comp["fax"].ToString(); }
							if (!String.IsNullOrEmpty(cont["mobile_phone"].ToString()))
							{ contact.PhoneNumbers[PhoneNumberKey.MobilePhone] = cont["mobile_phone"].ToString(); }
							if (!String.IsNullOrEmpty(cont["email"].ToString()))
							{ contact.EmailAddresses[EmailAddressKey.EmailAddress1] = cont["email"].ToString(); }
							DataRow cat = dscont.Tables["categories"].Rows.Find(comp["category_id"]);
							contact.Categories.Add(cat["category"].ToString());
							contact.Save(olfid);
							ItemId iid = contact.Id;
							contact = Contact.Bind(service, iid);
						}
					}
				}
			}
			catch (System.Exception e)
			{

			}
		}


		public static void FillSpreadsheet (DataSet dscont, DataGridView dgv, int pi, ProjectTypes ProjectType, ProgressBar pb2, Label lab_save)
		{
			uint h;
			//lab_save = lab_sa;
			StoreProgramTypes programtype = func.GetStoreProgramType(ProjectType);
			switch (programtype){
				case StoreProgramTypes.StoreLayout:
					{
						proj = dscont.Tables ["projects_remodel"];
						foldername = "2nd Floor Store Contacts";
					}
					break;
				case StoreProgramTypes.WalMartRealty:
					{
						proj = dscont.Tables [programtype.ToString()];
						foldername = "3rd Floor Store Contacts";
					}
					break;
			}

			int col;
			int strid = (int)proj.Rows[pi]["str_id"];
			filter = projcont.Select(string.Format("str_id = {0}", strid));
			int filtercount = filter.Length;
			DataTable strcontacts = new DataTable();
			string[] headername = new string[] {"Str Num", "Seq", "Store City", "Str St", "Job Role", "Full Name", "Company", "Address", "Suite",
				"City", "St", "Zip", "Direct", "Main", "Fax", "Mobile", "Email", "Category"};
			string[] alpha = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R" };
			int headercount = headername.Length;
			for (int a = 0; a < headercount; a++)
			{ strcontacts.Columns.Add(headername[a]); }


			//lab_save.Text = "Rebuilding contacts in Excel...";

			for (int f = 0; f < filter.Length; f++)
			{
				pb2.PerformStep();
				pb2.Refresh();
				lab_save.Text = "Rebuilding contacts in Excel...";
				lab_save.Refresh();
				DataRow pc = strcontacts.NewRow();
				DataRow jr = dscont.Tables["job_roles"].Rows.Find(filter[f].ItemArray[2]);
				DataRow cont = dscont.Tables["contacts"].Rows.Find(filter[f].ItemArray[3]);
				string name;
				if (String.IsNullOrEmpty(cont["middle_name"].ToString()))
				{ name = cont["first_name"].ToString() + " " + cont["last_name"].ToString(); }
				else
				{ name = cont["first_name"].ToString() + " " + cont["middle_name"].ToString() + " " + cont["last_name"].ToString(); }
				DataRow comp = dscont.Tables["companies"].Rows.Find(cont["company_id"]);
				DataRow cat = dscont.Tables["categories"].Rows.Find(comp["category_id"]);
				List<string> fields = new List<string> { proj.Rows[pi]["str_num"].ToString(), proj.Rows[pi]["str_seq"].ToString(), proj.Rows[pi]["str_city"].ToString(), proj.Rows[pi]["str_st"].ToString(),
					jr["job_role"].ToString(), name, comp["company_name"].ToString(), comp["street"].ToString(), comp["suite"].ToString(), comp["city"].ToString(), comp["state"].ToString(),
					comp["zip"].ToString(), cont["direct_phone"].ToString(), comp["main_phone"].ToString(), comp["fax"].ToString(), cont["mobile_phone"].ToString(),
					cont["email"].ToString(), cat["category"].ToString() };
				for (int d = 0; d < strcontacts.Columns.Count; d++)
				{ pc[headername[d]] = fields[d]; }
				strcontacts.Rows.Add(pc);
			}
			strcontacts.DefaultView.Sort = strcontacts.Columns["Full Name"].ToString();
			strcontacts = strcontacts.DefaultView.ToTable();
			strcontacts.DefaultView.Sort = strcontacts.Columns["Job Role"].ToString();
			strcontacts = strcontacts.DefaultView.ToTable();
			string city = proj.Rows[pi]["str_city"].ToString();
			if (city.Contains("\\"))
			{
				string[] citysplit;
				citysplit = city.Split(new Char[] { '\\' });
				city = citysplit[0] + citysplit[1];
			}

			string path = (@"u:\Reports\Project Contacts\3rd Floor Store Contacts\ " + city + " " + proj.Rows[pi]["str_st"].ToString() + " " +
			               proj.Rows[pi]["str_num"].ToString() + "-" + proj.Rows[pi]["str_seq"].ToString() + ".xlsx");
			xl = null;
			//creates excel file
			xl = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook, true);
			//creates the workbookpart and worksheetpart
			workbook = xl.AddWorkbookPart();
			worksheet = workbook.AddNewPart<WorksheetPart>("rId1");
			sharedstring = xl.WorkbookPart.AddNewPart<SharedStringTablePart>();
			sst = new SharedStringTable();
			sharedstring.SharedStringTable = sst;
			//create the workbook parts
			wb = new Workbook();
			FileVersion fv = new FileVersion() { ApplicationName = "Microsoft Office Excel" };
			sheets = new Sheets();
			sheet = new Sheet() { Id = xl.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Contacts" }; //xl.WorkbookPart.GetIdOfPart(worksheet)
			sheets.Append(sheet);
			wb.Append(fv);
			wb.Append(sheets);
			//creates the styles
			styles = xl.WorkbookPart.AddNewPart<WorkbookStylesPart>();
			styles.Stylesheet = GenerateStyleSheet();
			styles.Stylesheet.Save();
			//creates the worksheet parts
			ws = new Worksheet();
			hf = new HeaderFooter(
				new OddHeader()
				{
					Text = "Contacts for " + proj.Rows[pi]["str_city"].ToString() + ", " +
						proj.Rows[pi]["str_st"].ToString() + " - " + proj.Rows[pi]["str_num"].ToString() + "-" +
						proj.Rows[pi]["str_seq"].ToString()
				},
				new OddFooter()
			) { AlignWithMargins = false, DifferentFirst = false, DifferentOddEven = false };
			pagesetup = new PageSetup() { FitToWidth = 1, Orientation = OrientationValues.Landscape, PaperSize = 3 };
			sp = new SheetProperties();
			pagesetupprop = new PageSetupProperties() { FitToPage = true };
			sp.Append(pagesetupprop);
			ws.Append(sp);
			columns = new Columns(
				new Column() { Min = 1, Max = 1, Width = 10, CustomWidth = true },
				new Column() { Min = 2, Max = 2, Width = 5, CustomWidth = true },
				new Column() { Min = 3, Max = 3, Width = 20, CustomWidth = true },
				new Column() { Min = 4, Max = 4, Width = 6, CustomWidth = true },
				new Column() { Min = 5, Max = 5, Width = 40, CustomWidth = true },
				new Column() { Min = 6, Max = 6, Width = 25, CustomWidth = true },
				new Column() { Min = 7, Max = 7, Width = 35, CustomWidth = true },
				new Column() { Min = 8, Max = 8, Width = 40, CustomWidth = true },
				new Column() { Min = 9, Max = 9, Width = 14, CustomWidth = true },
				new Column() { Min = 10, Max = 10, Width = 20, CustomWidth = true },
				new Column() { Min = 11, Max = 11, Width = 5, CustomWidth = true },
				new Column() { Min = 12, Max = 16, Width = 18, CustomWidth = true },
				new Column() { Min = 17, Max = 17, Width = 40, CustomWidth = true },
				new Column() { Min = 18, Max = 18, Width = 30, CustomWidth = true }
			);
			ws.Append(columns);

			tablecolumns = new TableColumns() { Count = (UInt32)(headercount) };
			for (h = 0; h < headercount; h++)
			{ GenerateTableColumn(h + 1, headername[h].ToString()); }

			SheetData data = new SheetData();

			Row headerrow = new Row { RowIndex = (UInt32)1 };
			for (col = 0; col < strcontacts.Columns.Count; col++)
			{
				Cell cell = new Cell() { CellReference = alpha[col] + "1", DataType = CellValues.String, CellValue = new CellValue(headername[col].ToString()), StyleIndex = 3 };
				headerrow.Append(cell);
			}
			data.Append(headerrow);

			for (int r = 0; r < strcontacts.Rows.Count; r++)
			{
				int ri = r + 2;
				Row row = new Row { RowIndex = (UInt32)ri };

				for (col = 0; col < strcontacts.Columns.Count; col++)
				{
					Cell cell = null;
					if (col == 0 || col == 1 || col == 3 || col == 10 || col == 11 || col == 12 || col == 13 || col == 14 || col == 15)
					{
						cell = new Cell() { CellReference = alpha[col] + ri, DataType = CellValues.String, CellValue = new CellValue(strcontacts.Rows[r][headername[col].ToString()].ToString()), StyleIndex = 1 };
						row.Append(cell);
					}
					else
					{
						cell = new Cell() { CellReference = alpha[col] + ri, DataType = CellValues.String, CellValue = new CellValue(strcontacts.Rows[r][headername[col].ToString()].ToString()), StyleIndex = 2 };
						row.Append(cell);
					}
				}
				data.Append(row);
			}
			ws.Append(data);

			TableDefinitionPart tdp = worksheet.AddNewPart<TableDefinitionPart>("rId1");
			table = new Table()
			{
				Id = (UInt32)1U,
				HeaderRowCount = 1,
				DisplayName = "Contacts",
				Name = "Contacts",
				Reference = "A1:R" + (UInt32)(filter.Length + 1),
				BorderFormatId = 0,
				TotalsRowShown = false
			};
			table.Append(tablecolumns);
			TableStyleInfo tablestyle = new TableStyleInfo() { Name = "TableStyleLight1", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false, };
			table.Append(tablestyle);
			tps = new TableParts() { Count = (UInt32)1U };
			tp = new TablePart() { Id = "rId1" };
			tps.Append(tp);
			tdp.Table = table;
			ws.Append(pagesetup);
			ws.Append(hf);
			ws.Append(tps);
			worksheet.Worksheet = ws;
			ws.Save();
			xl.WorkbookPart.Workbook = wb;
			wb.Save();
			xl.Close();
			
		}

		private static Stylesheet GenerateStyleSheet()
		{
			return new Stylesheet(

				new Fonts(new DocumentFormat.OpenXml.Spreadsheet.Font(
					//index 0 = default font
					new FontSize() { Val = 8 },
					new FontName() { Val = StringValue.FromString("Calibri") })
				         ),

				new Fills(new Fill(
					//index 0 = default fill
					new PatternFill() { PatternType = PatternValues.None }),
				          new Fill(
				          	//index 1 = default fill of gray 125 required
				          	new PatternFill() { PatternType = PatternValues.Gray125 })),

				new Borders(new Border(
					//index 0 = default border
					new LeftBorder(),
					new RightBorder(),
					new TopBorder(),
					new BottomBorder(),
					new DiagonalBorder()),
				            //index 1 = top, bottom, left, right borders thin
				            new Border(
				            	new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new DiagonalBorder()),
				            //index 2 = top, bottom, left, right borders thin
				            new Border(
				            	new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
				            	new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
				            	new DiagonalBorder())),

				new CellFormats(
					//index = 0
					new CellFormat() { FontId = 0, FillId = 0, BorderId = 0, ApplyFont = true },

					//index 1 = regular font + thin borders + center alignment
					new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
					              ) { FontId = 0, FillId = 0, BorderId = 1, ApplyFont = true, ApplyBorder = true },

					//index 2 = regular font + thin borders + left alignment
					new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center }
					              ) { FontId = 0, FillId = 0, BorderId = 1, ApplyFont = true, ApplyBorder = true },

					//index 3 = regular font + thin borders + left alignment
					new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
					              ) { FontId = 0, FillId = 0, BorderId = 2, ApplyFont = true, ApplyBorder = true }


				),

				new DifferentialFormats(
					new DifferentialFormat(
						new Border(
							new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
							new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
							new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
							new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
							new DiagonalBorder())),

					new DifferentialFormat(
						//new DocumentFormat.OpenXml.Spreadsheet.Font(
						//new Bold(),
						//new FontSize() { Val = 8 },
						//new FontName() { Val = "Calibri" }
						//),
						new Border(
							new LeftBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
							new RightBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Thin },
							new TopBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
							new BottomBorder(new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }) { Style = BorderStyleValues.Medium },
							new DiagonalBorder())
						//new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }

					)) { Count = (uint)2 },
				new TableStyles() { Count = (UInt32)0, DefaultTableStyle = "TableStyleLight1" }
			);
		}

		public static void GenerateTableColumn(uint i, string name)
		{
			//Cell headercell = new Cell(new InlineString(new Text { Text = name.ToString() }));
			tablecolumn = new TableColumn() { Id = i, Name = name };
			tablecolumns.Append(tablecolumn);
		}

		private static int InsertSharedStringItem(string text, SharedStringTable sharedtable)
		{
			int ssi = 0;
			foreach (SharedStringItem item in sharedtable.Elements<SharedStringItem>())
			{
				if (item.InnerText == text)
				{
					return ssi;
				}
				ssi++;
			}
			sharedtable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
			return ssi;

		}

		public static void FolderView(ProgressBar pb)
		{
			try
			{
				#if!__MonoCS__
				Outlook.View v;
				Outlook.Application ol = new Outlook.Application();
				Outlook.MAPIFolder fldr = ol.GetNamespace("MAPI").GetDefaultFolder(Outlook.OlDefaultFolders.olPublicFoldersAllPublicFolders).Folders["001 Contacts"];
				Outlook.Folders contactfldrs = fldr.Folders;
				int olfldrcount = contactfldrs.Count;
				Outlook.MAPIFolder contactfldr = contactfldrs[2];
				Outlook.Folders strfldrs = contactfldr.Folders;
				Outlook.View view;
				Outlook.View newview;
				pb.Visible = true;
				int strfldrcount = strfldrs.Count;

				pb.Minimum = 1;
				pb.Maximum = strfldrcount;
				pb.Value = 1;
				pb.Step = 1;


				//<viewstyle>table-layout:fixed;width:100%;font-family:Segoe UI;font-style:normal;font-weight:normal;font-size:8pt;color:Black;font-charset:0</viewstyle>


				string projectcontactsview = @"<?xml version=""1.0"" ?>
                  <view type=""table"">
                  <viewname>ProjectContactsView</viewname>
                  <viewtime>216787283</viewtime>
                  <linecolor>8421504</linecolor>
                  <linestyle>3</linestyle>
                  <gridlines>1</gridlines>
                  <autosizing>0</autosizing>
                  <newitemrow>1</newitemrow>
                  <incelledit>1</incelledit>
                  <collapsestate />
                  <rowstyle>font-family:Calibri;font-size:9pt;background-color:White;color:Black</rowstyle>
                  <headerstyle>font-family:Calibri;font-weight:bold;font-size:10pt;background-color:#D3D3D3</headerstyle>
                  <previewstyle>font-family:Calibri;font-size:9pt</previewstyle>
                  <arrangement>
                  <autogroup>0</autogroup>
                  <persistgroups>1</persistgroups>
                  <enablexfc>0</enablexfc>
                  <collapseclient />
                  <collapseconv />
                  <upgradetoconvchangenum>1</upgradetoconvchangenum>
                  </arrangement>
                  <multiline>
                  <width>0</width>
                  <gridlines>0</gridlines>
                  <linestyle>0</linestyle>
                  </multiline>
                  <column>
                  <name>HREF</name>
                  <prop>DAV:href</prop>
                  <checkbox>1</checkbox>
                  </column>
                  <column>
                  <heading>Project</heading>
                  <prop>urn:schemas:contacts:roomnumber</prop>
                  <type>string</type>
                  <width>240</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>Project</userheading>
                  </column>
                  <column>
                  <heading>Job Role</heading>
                  <prop>urn:schemas:contacts:profession</prop>
                  <type>string</type>
                  <width>210</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>Job Role</userheading>
                  </column>
                  <column>
                  <maxrows>49545216</maxrows>
                  <heading>Full Name</heading>
                  <prop>urn:schemas:contacts:cn</prop>
                  <type>string</type>
                  <width>150</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  </column>
                  <column>
                  <heading>Job Title</heading>
                  <prop>urn:schemas:contacts:title</prop>
                  <type>string</type>
                  <maxrows>94044160</maxrows>
                  <width>252</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  </column>
                  <column>
                  <maxrows>138477568</maxrows>
                  <heading>Company</heading>
                  <prop>urn:schemas:contacts:o</prop>
                  <type>string</type>
                  <width>240</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  </column>
                  <column>
                  <heading>Street</heading>
                  <prop>urn:schemas:contacts:street</prop>
                  <type>string</type>
                  <width>240</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>Street</userheading>
                  </column>
                  <column>
                  <heading>City</heading>
                  <prop>urn:schemas:contacts:l</prop>
                  <type>string</type>
                  <width>120</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>City</userheading>
                  </column>
                  <column>
                  <heading>Zip</heading>
                  <prop>urn:schemas:contacts:postalcode</prop>
                  <type>string</type>
                  <width>72</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>Zip</userheading>
                  </column>
                  <column>
                  <heading>St</heading>
                  <prop>urn:schemas:contacts:st</prop>
                  <type>string</type>
                  <width>48</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>St</userheading>
                  </column>
                  <column>
                  <maxrows>182976512</maxrows>
                  <heading>Direct Phone</heading>
                  <prop>urn:schemas:contacts:officetelephonenumber</prop>
                  <type>string</type>
                  <width>120</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <shortheading>Business</shortheading>
                  <userheading>Direct Phone</userheading>
                  </column>
                  <column>
                  <heading>Main Phone</heading>
                  <prop>urn:schemas:contacts:office2telephonenumber</prop>
                  <type>string</type>
                  <width>120</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <shortheading>Business 2</shortheading>
                  <userheading>Main Phone</userheading>
                  </column>
                  <column>
                  <maxrows>227409920</maxrows>
                  <heading>Fax</heading>
                  <prop>urn:schemas:contacts:facsimiletelephonenumber</prop>
                  <type>string</type>
                  <width>120</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <userheading>Fax</userheading>
                  </column>
                  <column>
                  <maxrows>271843328</maxrows>
                  <heading>Mobile Phone</heading>
                  <prop>http://schemas.microsoft.com/mapi/proptag/0x3a1c001f</prop>
                  <type>string</type>
                  <width>120</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  <shortheading>Mobile</shortheading>
                  </column>
                  <column>
                  <heading>E-mail</heading>
                  <prop>http://schemas.microsoft.com/mapi/id/{00062004-0000-0000-C000-000000000046}/8084001f</prop>
                  <type>string</type>
                  <maxrows>316342272</maxrows>
                  <width>240</width>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>0</editable>
                  </column>
                  <column>
                  <maxrows>11075584</maxrows>
                  <heading>Categories</heading>
                  <prop>urn:schemas-microsoft-com:office:office#Keywords</prop>
                  <width>210</width>
                  <sortable>0</sortable>
                  <style>padding-left:3px;;text-align:left</style>
                  <editable>1</editable>
                  </column>
                  <orderby>
                  <order>
                  <heading>Job Role</heading>
                  <prop>urn:schemas:contacts:profession</prop>
                  <type>string</type>
                  <userheading>Job Role</userheading>
                  <sort>asc</sort>
                  </order>
                  <order>
                  <heading>Full Name</heading>
                  <prop>urn:schemas:contacts:cn</prop>
                  <type>string</type>
                  <sort>asc</sort>
                  </order>
                  </orderby>
                  <groupbydefault>0</groupbydefault>
                  <previewpane>
                  <markasread>0</markasread>
                  </previewpane>
                  </view>";

				for (int vc = 1; vc < olfldrcount; vc++)
				{

				}

				//v = null;
				//v.XML = projectcontactsview;
				//v.Name = "ProjectContactsView";

				//int contvcount = fldr.Views.Count;
				//for (int vc = 1; vc < contvcount + 1; vc++)
				//{
				//    string vw = fldr.Views[vc].Name;
				//}
				//v = fldr.Views[2];

				for (int f = 1; f < strfldrcount + 1; f++)
				{
					pb.PerformStep();
					int vcount = strfldrs[f].Views.Count;

					int b;
					for (b = 1; b < vcount + 1; b++)
					{
						string fn = strfldrs[f].Name;
						view = strfldrs[f].Views[b];
						if (view.Name == "ProjectContactsView")
						{
							view.Apply();
							//string vxml = view.XML;
							//view.Apply();
							//break;
						}
						//else if (b == vcount && view.Name != "ProjectContactsView")
						//{
						
						//    newview = strfldrs[f].Views.Add("ProjectContactsView", Outlook.OlViewType.olTableView, Outlook.OlViewSaveOption.olViewSaveOptionAllFoldersOfType);
						
						//    newview.XML = projectcontactsview;
						//    newview.Save();
						//    //newview.Copy("ProjectContactsView", Outlook.OlViewSaveOption.olViewSaveOptionAllFoldersOfType);
						//    newview.Apply();
						//    break;
						//}
					}
					//if (b == vcount + 1)
					//{
					//    //v.Copy("ProjectContactsView", Outlook.OlViewSaveOption.olViewSaveOptionAllFoldersOfType);
					//    //v.Copy("ProjectContactsView1", Outlook.OlViewSaveOption.olViewSaveOptionThisFolderEveryone);
					//    //v.Save();
					//}
					//strfldr = strfldrs[f];

					//strfldr.Views["ProjectContactsView1"].Apply();
				}
				pb.Value = 1;
				pb.Visible = false;
				#endif
			}
			
			catch (Exception ex)
			{
				string subject = "func Folder View Error";
				ErrorHandling.ErrorEmail(subject, ex);
			}
		}

		public static void OLcontacts()
		{}

		public static void cbocompsort(ComboBox cbocomp)
		{
			cbocomp.DisplayMember = "companies.comp_sel";
		}

		public static void returnkey(KeyPressEventArgs e)
		{
			if (e.KeyChar == (char)Keys.Return)
			{}
		}

		public static void current_thread()
		{

		}

		public static void gettime(string when)
		{
			if (when == "top")
			{
				string top = DateTime.Now.ToLongTimeString();
			}
		}
		
		public static StoreProgramTypes GetStoreProgramType (ProjectTypes ProjectType)
		{
			StoreProgramTypes result = StoreProgramTypes.None;
			if (ProjectType == 0) { return result;
			} else if ((uint)ProjectType < 16384u) {
				result = StoreProgramTypes.WalMartRealty;
			} else if (16383u < (uint)ProjectType) {
				result = StoreProgramTypes.StoreLayout;
			}
			
			/*
 			var typevalues = Enum.GetValues(typeof(ProjectTypes));
			foreach (ProjectTypes value in typevalues) {
				if ((ProjectType & value) == value) {
					switch (value) {
							#region cases
						case ProjectTypes.Remodel:
						case ProjectTypes.Special:
						case ProjectTypes.Tenant:
						case ProjectTypes.Fixturing:
						case ProjectTypes.RemodelC:
							result = StoreProgramTypes.StoreLayout;
							break;
						case ProjectTypes.TakeOver:
						case ProjectTypes.Expansion:
						case ProjectTypes.GroundUp:
						case ProjectTypes.LandBank:
						case ProjectTypes.OnSiteRelo:
						case ProjectTypes.Relo:
							result = StoreProgramTypes.WalMartRealty;
							break;
							#endregion
					}
				}
			}*/
			return result;
		}
	}
}
